using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Linq;
using System.Xml;
using System.IO;

namespace GuavaData
{
    public class AppData : Object
    {
        
        public static DataSet ds;
        public static GTree gt;
        public static Schema sch;
        public static Channel c;
        public static bool useDB = true;
        public static string dbsource, dbcatalog;
        public static string assemblyPath;
        public static bool testForEnum = false;

        public static void Init(GuavaForm root, Guid g, string path, string source, string catalog, Channel chan)
        {
            DBConnection.Initialize(source, catalog, null);
            assemblyPath = path;
            gt = GTree.Generate(root);
            sch = gt.ToSchema();

            ds = gt.ToDataSet();
            dbsource = source;
            dbcatalog = catalog;
           // c = DBConnection.ChannelFromDB();
            c = chan;
            XmlDocument doc = gt.ToXML();
            doc.Save("./gtree.xml");

            SchemaController sc = new SchemaController(path, "./schema_log.xml", sch, c);
            sc.RetriveDBSchema(sch);
            c.PoseTransaction(sc.CreateSchemaUpdateTransaction());
            // Make sure that there is the right row for the root node
            
            FetchRowOrCreate(root.GName, g);
           // c.PreseedQueries(ds);
            
        }
        public static XmlDocument sendGtree()
        {
            //XmlDocument doc = gt.ToXML();
            XmlDocument doc = new XmlDocument();
            doc.Load("C:\\Users\\ekost\\Desktop\\guava\\CORIDemo\\bin\\Debug\\gtree.xml");
            StringWriter sw = new StringWriter();
            XmlTextWriter tx = new XmlTextWriter(sw);
            doc.WriteTo(tx);
            //doc.Save(tx);
            //return sw.ToString();
            return doc;
        }
        public static void SetForServerData(string path, string source, string catalog, Channel chan)
        {
            assemblyPath = path;
            dbsource = source;
            dbcatalog = catalog;
            c = chan;
            testForEnum = false;

        }
        public static void PushData()
        {
            foreach (var tab in sch.tables)
            {
                Command com = new Command();
                string alias = com.nextTableAlias(tab.TableName);
                List<string> cols = new List<string>();
                List<string> colAlias = new List<string>();
                List<Domain.Domain> doms = new List<Domain.Domain>();
                foreach (var col in tab.columns)
                {
                    cols.Add(col.Name);
                    doms.Add(col.dom);
                }
                TableRef tref = new TableRef(dbcatalog + ".dbo." + tab.TableName, alias, cols, cols, doms);
                Insert i = new Insert(tab.TableName, cols, tref);
                c.PoseQueryCommand(new Command(i));
            }
        }

        public static int CreateRow(string table, Guid id)
        {
            DataTable dt = ds.Tables[table];
            DataRow drnew = dt.NewRow();
            dt.Rows.Add(drnew);
            drnew["id"] = id;
            return dt.Rows.Count - 1;
        }

        public static int CreateRow(string table, Guid id, Guid fk)
        {
            DataTable dt = ds.Tables[table];
            DataRow drnew = dt.NewRow();
            dt.Rows.Add(drnew);
            drnew["id"] = id;
            drnew["fk"] = fk;
            return dt.Rows.Count - 1;
        }

        public static void DeleteRow(string table, Guid id)
        {
            try
            {
                int i = FetchRow(table, id);

                // Remove the row from memory
                ds.Tables[table].Rows.RemoveAt(i);
            }
            catch (Exception e)
            {
                if (!(e is DeletedRowInaccessibleException)) throw e;
            }
            
            // Generate delete statement
            if (useDB)
            {
                // Generate NonQuery object
                Delete d = new Delete(table);
                d.AddCondition("id", Condition.NewCondition(ConditionType.EQ, id));
                c.PoseTransaction(new Transaction(d));
            }
        }

        public static DataTable FetchAllRows(string table)
        {
            if (!useDB) return new DataTable();
            DataTable dt = ds.Tables[table];

            // Create the appropriate query: select * from table
            Command com = new Command();
            string alias = com.nextTableAlias(table);
            TableRef tr = new TableRef(table, alias);
            com.root = tr;
            for (int j = 0; j < dt.Columns.Count; j++)
                tr.AddColumn(dt.Columns[j].ColumnName, alias + "_" + dt.Columns[j].ColumnName, 
                    (Domain.Domain)dt.Columns[dt.Columns[j].ColumnName].ExtendedProperties[Operator.DomainFlag]);
            DataSet res = c.PoseQueryCommand(com);

            // Verify that there is only one table returned
            if (res.Tables.Count != 1) throw new Exception("Tried to pull one row from the database, but received " + res.Tables.Count.ToString() + " tables.");

            return res.Tables[0];
        }
        
        public static void FetchRowsByFK(string table, Guid fk)
        {
            if (!useDB) return;
            DataTable dt = ds.Tables[table];

            // Create the appropriate query: select * from table
            Command com = new Command();
            string alias = com.nextTableAlias(table);
            TableRef tr = new TableRef(table, alias);
            for (int j = 0; j < dt.Columns.Count; j++)
            {
                tr.AddColumn(dt.Columns[j].ColumnName, alias + "_" + dt.Columns[j].ColumnName, 
                    (Domain.Domain)dt.Columns[dt.Columns[j].ColumnName].ExtendedProperties[Operator.DomainFlag]);
            }

            Filter f = new Filter(tr);
            for (int j = 0; j < dt.Columns.Count; j++)
            {
                if (dt.Columns[j].ColumnName == "fk")
                    f.AddCondition(alias + "." + dt.Columns[j].ColumnName, Condition.NewCondition(ConditionType.EQ, fk));
            }
      
            com.root = f;
            DataSet res = c.PoseQueryCommand(com);

            // Verify that there is only one table returned
            if (res.Tables.Count != 1) throw new Exception("Tried to pull one row from the database, but received " + res.Tables.Count.ToString() + " tables.");
            
            // Remove existing entries in the table
            for (int j = 0; j < dt.Rows.Count; j++)
            {
                while ((j < dt.Rows.Count) && (dt.Rows[j]["fk"].ToString() == fk.ToString())) dt.Rows.RemoveAt(j);
            }
            
            // Yank the rows from the result and add it to the in-memory database
            foreach (DataRow dr in res.Tables[0].Rows)
            {
                DataRow dnew = dt.NewRow();
                foreach (DataColumn dc in dt.Columns)
                {
                    dnew[dc.ColumnName] = dr[String.Concat(alias, "_", dc.ColumnName)];
                }
                dt.Rows.Add(dnew);
                dnew.AcceptChanges();
            }

            return;
        }

        /// <summary>
        /// Retrieve rows that fit conditions into the table specified by the first parameter.  Clears existing rows in memory.
        /// </summary>
        /// <param name="table">The name of the table to fetch rows from.</param>
        /// <param name="columns">The name of the columns to apply conditions to.</param>
        /// <param name="conditions">The conditions to be satisfied.  Conditions are joined by AND.</param>
        public static void FetchRowsByConditions(string table, List<string> columns, List<Condition> conditions)
        {
            if (!useDB) return;
            DataTable dt = ds.Tables[table];

            // Create the appropriate query: select * from table where COND1 AND COND2 AND ... AND CONDn
            Command com = new Command();
            string alias = com.nextTableAlias(table);
            TableRef tr = new TableRef(table, alias);
            for (int j = 0; j < dt.Columns.Count; j++)
            {
                tr.AddColumn(dt.Columns[j].ColumnName, alias + "_" + dt.Columns[j].ColumnName,  
                    (Domain.Domain)dt.Columns[dt.Columns[j].ColumnName].ExtendedProperties[Operator.DomainFlag]);
            }

            // Add the filters
            Operator o = tr;
            for (int i = 0; i < columns.Count; i++)
            {
                o = new Filter(o);
                (o as Filter).AddCondition(alias + "_" + columns[i], conditions[i]);
            }

            com.root = o;
            DataSet res = c.PoseQueryCommand(com);

            // Verify that there is only one table returned
            if (res.Tables.Count != 1) throw new Exception("Tried to pull one row from the database, but received " + res.Tables.Count.ToString() + " tables.");

            // Remove existing entries in the table
            dt.Rows.Clear();

            // Yank the rows from the result and add it to the in-memory database
            foreach (DataRow dr in res.Tables[0].Rows)
            {
                DataRow dnew = dt.NewRow();
                foreach (DataColumn dc in dt.Columns)
                {
                    dnew[dc.ColumnName] = dr[String.Concat(alias, "_", dc.ColumnName)];
                }
                dt.Rows.Add(dnew);
                dnew.AcceptChanges();
            }

            return;
        }

        public static int FetchRow(string table, Guid id)
        {
            DataTable dt = ds.Tables[table];
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                if (id.Equals(dt.Rows[i]["id"])) return i;
            }

            if (!useDB) throw new Exception("Could not find selected row in memory.");

            // Create the appropriate query: select * from table where id = id
            Command com = new Command();
            string alias = com.nextTableAlias(table);
            TableRef tr = new TableRef(table, alias);

            for (int j = 0; j < dt.Columns.Count; j++)
            {
                tr.AddColumn(dt.Columns[j].ColumnName, alias + "_" + dt.Columns[j].ColumnName,  
                    (Domain.Domain)dt.Columns[dt.Columns[j].ColumnName].ExtendedProperties[Operator.DomainFlag]);
            }
            Filter f = new Filter(tr);
            f.AddCondition("id", Condition.NewCondition(ConditionType.EQ, id));
            com.root = f;
            DataSet res = c.PoseQueryCommand(com);

            // Verify that there is only one row returned
            if (res.Tables.Count != 1) throw new Exception("Tried to pull one row from the database, but received " + res.Tables.Count.ToString() + " tables.");
            if (res.Tables[0].Rows.Count != 1) throw new Exception("Tried to pull one row from the database, but received " + res.Tables[0].Rows.Count.ToString() + " rows.");
            
            // Yank the row from the result and add it to the in-memory database
            DataRow dr = res.Tables[0].Rows[0];
            DataRow dnew = dt.NewRow();
            foreach (DataColumn dc in dt.Columns)
            {
                dnew[dc.ColumnName] = dr[String.Concat(alias, "_", dc.ColumnName)];
            }
            dt.Rows.Add(dnew);
            return dt.Rows.Count - 1;
        }

        public static int FetchRowOrCreate(string table, Guid id)
        {
            DataTable dt = ds.Tables[table];
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                if (id.Equals(dt.Rows[i]["id"])) return i;
            }

            if (!useDB) return CreateRow(table, id);

            // If it gets here, the row is not in memory, so we need to fetch from the DB
            // Create the appropriate query
            Command com = new Command();
            string alias = com.nextTableAlias(table);
            TableRef tr = new TableRef(table, alias);
            Filter f = new Filter(tr);
            com.root = f;

            for (int j = 0; j < dt.Columns.Count; j++)
            {
                tr.AddColumn(dt.Columns[j].ColumnName, alias + "_" + dt.Columns[j].ColumnName,    
                    (Domain.Domain)dt.Columns[dt.Columns[j].ColumnName].ExtendedProperties[Operator.DomainFlag]);
                if (dt.Columns[j].ColumnName == "id")
                    f.AddCondition((alias != "" ? alias + ".":"") + dt.Columns[j].ColumnName, Condition.NewCondition(ConditionType.EQ, id));
            }
            DataSet res = c.PoseQueryCommand(com);

            // Verify that there is only one row returned
            if (res.Tables.Count != 1) throw new Exception("Tried to pull one row from the database, but received " + res.Tables.Count.ToString() + " tables.");
            if (res.Tables[0].Rows.Count > 1) throw new Exception("Tried to pull one row from the database, but received " + res.Tables[0].Rows.Count.ToString() + " rows.");
            if (res.Tables[0].Rows.Count == 0)
            {
                // There wasn't a row found in the database, so we're creating one
                DataRow drnew = dt.NewRow();
                dt.Rows.Add(drnew);
                drnew["id"] = id;
                CommitRow(table, id, DMLType.INSERT);
                return dt.Rows.Count - 1;
            }

            // Yank the row from the result and add it to the in-memory database
            DataRow dr = res.Tables[0].Rows[0];
            DataRow dnew = dt.NewRow();
            foreach (DataColumn dc in dt.Columns)
            {
                dnew[dc.ColumnName] = dr[String.Concat(alias, "_", dc.ColumnName)];
            }
            dt.Rows.Add(dnew);
            return dt.Rows.Count - 1;
        }

        public static void CommitRow(string table, Guid id, DMLType t)
        {
            // If we're not using the DB, exit
            if (!useDB) return;
            
            // Grab the right row from the right table
            DataTable dt = ds.Tables[table];

            int i;
            for (i = 0; i < dt.Rows.Count; i++)
            {
                if (id.Equals(dt.Rows[i]["id"])) break;
            }
            
            // If id not found, throw error
            if (i >= dt.Rows.Count) throw new Exception("Could not find id " + id.ToString() + " in table " + table + ".");

            DataRow dr = dt.Rows[i];



            // Create DML command
            switch (t)
            {
                case DMLType.DELETE:
                    Delete d = new Delete(table);
                    d.AddCondition("id", Condition.NewCondition(ConditionType.EQ, id));
                    c.PoseTransaction(new Transaction(d));
                    break;

                case DMLType.INSERT:
                    RowConstant rc = new RowConstant();
                    foreach (DataColumn dc in dt.Columns)
                    {
                        if (dr[dc] == null || Convert.IsDBNull(dr[dc]))
                            continue;
                        rc.AddValue(dc.ColumnName, (Domain.Domain)dc.ExtendedProperties[Operator.DomainFlag], dr[dc]);
                    }
                    Insert ins = new Insert(table, new List<string>(rc.exposedColumns), rc);
                    c.PoseTransaction(new Transaction(ins));
                    break;

                case DMLType.UPDATE:
                    rc = new RowConstant();
                    foreach (DataColumn dc in dt.Columns)
                    {
                        if (dc.ColumnName == "id")
                            continue;
                        rc.AddValue(dc.ColumnName, (Domain.Domain)dc.ExtendedProperties[Operator.DomainFlag], dr[dc]);
                    }
                    Update up = new Update(table, new List<string>(rc.exposedColumns), rc);
                    up.AddCondition("id", Condition.NewCondition(ConditionType.EQ, id));
                    c.PoseTransaction(new Transaction(up));
                    break;
            }



            //// Generate NonQuery object
            //NonQuery nq = new NonQuery(gt);
            //nq.Add(table, id, t);
            
            //// On insert or update, we need to add the values for the data row
            //if ((t == NonQueryType.INSERT) || (t == NonQueryType.UPDATE))
            //{
            //    for (int j = 0; j < dt.Columns.Count; j++)
            //    {
            //        if (dt.Columns[j].ColumnName == "id") continue;
            //        nq.AddValue(dt.Columns[j].ColumnName, dr[j]);
            //    }
            //}

            //// Execute the command
            //c.ApplyDML(nq);

            return;
        }
    }
}
